package org.example.service;
import com.mysql.jdbc.StringUtils;
import org.example.dao.SuppliersDao;
import org.example.entity.Supplier;
import org.example.tools.jdbcUtils;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
public class SupplierService implements SuppliersDao {
    @Override
    public int addSupplier(Supplier supplier) throws SQLException, ClassNotFoundException {
        //建立连接
        Connection conn = jdbcUtils.getConnection();
        //增加操作
        String sql = "insert into suppliers values(null,'"+supplier.getName()+"','"+supplier.getAddress()+"','"+supplier.getTelephone()+"')";
        return jdbcUtils.executeInsert(sql,conn);
    }

    @Override
    public int deleteSupplier(Supplier supplier) throws SQLException, ClassNotFoundException {
        //建立连接
        Connection conn = jdbcUtils.getConnection();
        //删除操作
        String sql = "delete from suppliers where id="+supplier.getId();
        return jdbcUtils.executeUpdate(sql,conn);

    }

    @Override
    public int updateSupplier(Supplier supplier) throws SQLException, ClassNotFoundException {
        //建立连接
        Connection conn = jdbcUtils.getConnection();
        //更新操作
        String sql = "update suppliers set name='"+supplier.getName().toString()+"',addr='"+supplier.getAddress().toString()+"',phone='"+supplier.getTelephone().toString()+"' where id="+supplier.getId();
        System.out.println(sql);
        return jdbcUtils.executeUpdate(sql,conn);
    }

    @Override
    public Supplier queryOneSupplier(Supplier supplier) throws SQLException, ClassNotFoundException {
                //建立连接
        Connection conn = jdbcUtils.getConnection();
        //查询
        String sql = "select * from suppliers where id="+supplier.getId();
        ResultSet res = jdbcUtils.executeQuery(sql,conn);
        Supplier target = new Supplier();
        while (res.next()){
            target.setId(supplier.getId());
            target.setName(res.getString("name"));
            target.setAddress(res.getString("addr"));
            target.setTelephone(res.getString("phone"));
        }
        return target;
    }

    public Supplier queryMatchSupplier(Supplier supplier) throws SQLException, ClassNotFoundException {
        //建立连接
        Connection conn = jdbcUtils.getConnection();
        //查询
        String sql = "select * from suppliers where name like '%"+supplier.getName()+"%'";
        ResultSet res = jdbcUtils.executeQuery(sql,conn);
        Supplier target = new Supplier();
        while (res.next()){
            target.setId(supplier.getId());
            target.setName(res.getString("name"));
            target.setAddress(res.getString("addr"));
            target.setTelephone(res.getString("phone"));
        }
        return target;
    }
    @Override
    public List<Supplier> queryAllSuppliers(int pageIndex,int pageNumber) throws SQLException, ClassNotFoundException {
        //建立连接
        Connection conn = jdbcUtils.getConnection();
        //定义一个结果集列表
        List<Supplier> suppliersList = new ArrayList<>();
        //查询操作
        String sql = "select * from suppliers limit "+pageIndex*pageNumber+","+pageNumber;  //pageNumber每页固定显示条数
        ResultSet res = jdbcUtils.executeQuery(sql,conn);
        while(res.next()){
            Supplier target = new Supplier();
            target.setId(res.getInt("Id"));
            target.setName(res.getString("name"));
            target.setAddress(res.getString("addr"));
            target.setTelephone(res.getString("phone"));
            suppliersList.add(target);
        }
       return suppliersList;
    }
    public int queryAllSuppliersCount(int pageNumber) throws SQLException, ClassNotFoundException {
        //建立连接
        Connection conn = jdbcUtils.getConnection();
        //定义统计个数
        int count=0;
        //查询操作
        String sql = "select count(Id) as total from suppliers";  //pageNumber每页固定显示条数
        ResultSet res = jdbcUtils.executeQuery(sql,conn);
        while(res.next()){
            count = res.getInt("total");
        }
        return count;
    }
}
